ASCII Flat File Format Requirements

The following example is provided to give a general idea of what is meant by the term “flat file” (columns contain data elements and rows contain complete records of individual observations).  But it is important to understand that there is a lot of flexibility in the details, for example the columns present and their order, the presence of a header row, etc.

Figure 2.1: Example of Flat File Translatable Format

Delimiters

The flat file should be an ASCII text file (i.e. NOT in .xls format or other binary formats) and should be tab- or comma-delimited.  If the flat file is in binary format, is space delimited or contains fixed-length fields, it should be reformatted to a tab- or comma-delimited text format before running the Sanitas importer (most spreadsheets can reformat various data files to tab- or comma-delimited files).

Fields

The flat file should contain at least the following fields, in any order:

• well/monitoring point identifier (The name must be spelled and/or abbreviated consistently throughout the flat file, otherwise the translator will treat two different spellings as two different well/monitoring points);

• sample date (The format of the date must be consistent throughout the flat file);

• constituent name (The name must be spelled and/or abbreviated consistently throughout the flat file, otherwise the translator will treat two different spellings of the same constituent as two different constituents.  For example, “silver” and “Ag” will be treated as two different constituents.); and

• Sample value.

The flat file may contain the following optional fields:

• Units name;

• Flags;

• MDL and/or PQL (or simply RL); and

• CAS number.

Any additional fields will be ignored by the translator.

Field Formats

The Sanitas Data Translator is flexible, and will alert you to any improprieties in your data.  We suggest you try translating your data before you worry about the details below.

Well or Monitoring Point name:

• may not contain spaces (use underscores if necessary);

• may contain dashes; and

• may be up to 16 characters in length.

Sample Date:

• may be in any of several different numeric formats (dashes “-“ may be substituted for slashes “/”):

1. mm/dd/yy

2. mm/dd/yyyy

3. mmddyy

4. mmddyyyy

5. yymmdd

6. yyyymmdd

7. dd/mm/yy

8. dd/mm/yyyy

9. ddmmyy

10. ddmmyyyy

• may not use alpha characters (i.e.: Jan, Feb, etc.).

Constituent name:

• may be any number of words;

• may contain spaces, commas, etc.; and

• may be any length “within reason”, which is to say that constituent names exceeding 30 or 40 characters may be truncated or run off the page in printed reports.

Units name (i.e.; mg/l, ug/l, etc.):

• may not contain any spaces; and

• may be up to 20 characters in length

Sample value must be one of the following three types:

• an integer or floating point number (with a maximum precision of ten decimal places).  Values with decimal precision exceeding ten places may need to be converted to smaller units.  For example, the value 0.00000125 mg/l could be changed to 0.00125 ug/l.  Alternately, scientific notation may be used for very large or very small values.  The units for a constituent need not be consistent, as the translator can convert common units.

• a number preceded by a “<”, “ND”, or “ND<”, which Sanitas interprets as a nondetect with the number portion being the method detection limit (MDL) or practical quantitation limit (PQL), as needed (for more detailed information about how Sanitas handles nondetects, please refer to “Non-Detect / Trace Handling” on the Data tab of Configure Sanitas); or

• an “n/a” which signifies that there was no sample taken for this date/well/constituent combination.

Flags (short, user-defined codes attached to a specific data item for data qualification or other tracking purposes, and/or to define nondetect and/or trace values):

• should generally be no longer than two characters; and

• should generally not use the characters D, d, O, or o, as these characters are used by Sanitas to denote duplicate and outlier values (an exception is that O can be used in conjunction with automatic outlier removal to exclude values that the outlier test is not flagging but that the user feels are in fact outliers); and

• may contain ND or U or < to indicate non-detects, and J or TR to indicate trace values.  If so, non-detects become <RL in the Sanitas data file, that is, the original flag is not carried over (and adding a ND or U or < in the data editor in Sanitas does NOT create a non-detect).  Trace values may carry over the flag, depending on options (and values flagged in the Sanitas data file with J or TR generally can be considered trace values).

Duplicate Values

The Sanitas Data Translator averages multiple values for the same date/well/constituent.  When Sanitas encounters duplicate (triplicate or quadruplicate) values that are categorically different with regard to quantitation, the component values are averaged in the following manner:

1. If any of the components are detected and quantified (i.e., a “detect”), Sanitas will interpret the resultant averaged value as a detect;
2. If all components are censored but at least one is a trace value (i.e., detected but not quantified), the highest trace value will be used;
3. When there are differing detection limits (MDLs and/or PQLs) for trace values within the same sampling event, the highest trace value is used in lieu of averaging.

The following examples illustrate how Sanitas interprets these sets of sampling event values, assuming the <MDL+PQL trace syntax is selected and no non-default Alternate Reporting Limits behavior is in use:

Figure 2.2: Example of How Sanitas Interprets Sets of Sampling Event Values